import math
import numpy as np
from django.http.response import JsonResponse
from django.views.decorators.http import require_http_methods

import sys, MySQLdb
import json
import api.domains as domains
sys.path.insert(0, '..')
from mysite import db_config
from utils import functions as f, http_response, custom_utils


# np.set_printoptions(precision=2, suppress=True)


@require_http_methods(['GET'])
def avg_problem_percentage(request):
    """
    各公司平均问题占比
    :param request:
    :return:
    """
    # 接口返回值列表
    data = []
    data_quarter = ['quarter']
    data_company = []

    # 获取所有年所有季度
    year = f.query_data_year()
    quarter = []
    for y in year:
        q = f.query_data_quarter(y)
        quarter.extend([(y, i) for i in q])
        [data_quarter.append(str(y) + 'Q' + str(i)) for i in q]
    data.append(data_quarter)

    # 查询各公司每季度数据
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
            data_company = [company]
            sql = f"""select round(sum(a.problem_count)/sum(a.item_count)*100,2)
                    from check_result_{company} a,dim_date b
                    where a.RISK_MARKET_ITEM='是'
                    and DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
                    and (b.year,b.quarter) in {tuple(quarter)}
                    group by b.year,b.quarter"""
            curs.execute(sql)
            result = curs.fetchall()
            [data_company.append(str(r[0])) for r in result]
            data.append(data_company)
        return JsonResponse(data, safe=False)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def same_problem_top5(request):
    """
    各公司同类问题Top 5统计
    :param request:
    :return:
    """
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')

    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        # 查询风险集市相关的相同的检核项的问题占比总和
        sql = f"""select check_item,count(*) cnt,sum(problem_per) from (
                select check_item,problem_per,check_date from check_result_xt where risk_market_item='是' and problem_per is not null
                union
                select check_item,problem_per,check_date from check_result_zc where risk_market_item='是' and problem_per is not null
                union
                select check_item,problem_per,check_date from check_result_db where risk_market_item='是' and problem_per is not null
                union
                select check_item,problem_per,check_date from check_result_jk where risk_market_item='是' and problem_per is not null
                union
                select check_item,problem_per,check_date from check_result_jj1 where risk_market_item='是' and problem_per is not null
                union
                select check_item,problem_per,check_date from check_result_jj2 where risk_market_item='是' and problem_per is not null
                union
                select check_item,problem_per,check_date from check_result_jz where risk_market_item='是' and problem_per is not null
                ) a, dim_date b
            where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
            and b.year={year}
            and b.quarter={quarter}
            and b.month={month}
            and b.day={day}
            group by check_item
            having count(*)>1
            order by 3 desc,2 desc"""
        curs.execute(sql)
        result = curs.fetchall()

        check_item = []
        total_problem = []
        [check_item.append(r[0]) for r in result]
        [total_problem.append(float(str(r[2]))) for r in result]

        # 取top4问题项及占比
        top4_item = check_item[0:4]
        top4_problem = total_problem[0:4]
        other_problem = sum(total_problem[4:])
        # 合并 其他项
        top4_item.append('其他')
        top4_problem.append(other_problem)
        data = {
            'name': top4_item,
            'value': top4_problem
        }
        return JsonResponse(data)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def subcompany_data_percentage(request):
    """
    各公司数据量占比
    :param request:
    :return:
    """
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')

    data = []
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
            sql = f"""select sum(distinct item_count) from check_result_{company} a,
                        (
                            select max(a.check_version) check_version
                            from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
                            and b.year={year}
                            and b.quarter={quarter}
                            and b.month={month}
                            and b.day={day}
                        ) b
                        where a.check_version=b.check_version
                        and a.risk_market_item='是'"""
            curs.execute(sql)
            result = curs.fetchone()
            if result[0] is None:
                data.append({'name': company, 'value': 0})
            else:
                data.append({'name': company, 'value': float(str(result[0]))})
        return JsonResponse(data, safe=False)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


def count_db_rows(request):
    """统计各类数据库数据量
    """
    quarter = request.GET.get('quarter')

    data = [{
        "name": "MySQL",
        "value": np.random.randint(1000, 99999),
    },
        {
            "name": "Oracle",
            "value": np.random.randint(1000, 99999)
        },
        {
            "name": "SQL server",
            "value": np.random.randint(1000, 99999)
        },
        {
            "name": "HBase",
            "value": np.random.randint(1000, 99999)
        },
    ]
    return JsonResponse(data, safe=False)


@require_http_methods(['GET'])
def data_overview_total(request):
    """
    统计风险集市相关 总数据量、总问题数据量、总问题占比
    :param request:
    :return:
    """
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')

    all_cnt = 0
    problem_cnt = 0

    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
            sql = f"""select sum(a.item_count),sum(a.problem_count) from check_result_{company} a,
                        (
                            select max(a.check_version) check_version
                            from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
                            and b.year={year}
                            and b.quarter={quarter}
                            and b.month={month}
                            and b.day={day}
                        ) b
                        where a.check_version=b.check_version
                        and a.risk_market_item='是'"""
            curs.execute(sql)
            result = curs.fetchone()
            if result[0] is None:
                continue
            else:
                all_cnt = all_cnt + result[0]
                problem_cnt = problem_cnt + result[1]

        response = {
            'all_cnt': all_cnt,
            'problem_cnt': problem_cnt,
            'problem_per': round(problem_cnt / all_cnt * 100, 2)
        }
        return JsonResponse(response)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def data_overview_company(request):
    """
    统计风险集市相关 各公司 检核数据量、问题数据量、问题数据占比
    :param request:
    :return:
    """
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')

    data = []
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        for company in ('xt', 'zc', 'db', 'jk', 'jj1', 'jj2', 'jz'):
            sql = f"""select sum(a.item_count),sum(a.problem_count),round(sum(a.problem_count)/sum(a.item_count)*100,2)
                        from check_result_{company} a,
                        (
                            select max(a.check_version) check_version
                            from check_result_{company} a,dim_date b where DATE_FORMAT(a.check_date,'%Y%m%d') = b.day_id
                            and b.year={year}
                            and b.quarter={quarter}
                            and b.month={month}
                            and b.day={day}
                        ) b
                        where a.check_version=b.check_version
                        and a.risk_market_item='是'"""
            curs.execute(sql)
            result = curs.fetchone()
            data.append([company, result[0], result[1], result[2]])
        return JsonResponse(data, safe=False)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def data_overview_company_trend(request):
    """
    统计风险集市相关 各公司 检核数据量、问题数据量、问题数据占比
    :param request:
    :return:
    """
    year = request.GET.get('year')
    month = request.GET.get('month')
    day = request.GET.get('day')
    company = request.GET.get('company')

    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        sql = f"""select round(sum(problem_count)/sum(item_count)*100,2),check_version
                    from check_result_{company}
                    where risk_market_item='是'
                    and check_date < date_add('{year}-{month}-{day}',interval 1 day)
                    group by check_version
                    order by check_version asc"""
        curs.execute(sql)
        result = curs.fetchall()
        result = [r[0] for r in result]
        return JsonResponse(result, safe=False)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def total_trend(request):
    """
    显示集团总问题占比走势
    :param request:
    :return:
    """
    value = []
    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        sql = f"""select DATE_FORMAT(a.check_date,'%Y-%m-%d'),
                        round(sum(a.problem_count)/sum(a.item_count)*100,2),
                        count(distinct company) from
                (
                select 'xt' company,item_count,problem_count,check_date from check_result_xt where risk_market_item='是'
                union
                select 'zc' company,item_count,problem_count,check_date from check_result_zc where risk_market_item='是'
                union
                select 'db' company,item_count,problem_count,check_date from check_result_db where risk_market_item='是'
                union
                select 'jk' company,item_count,problem_count,check_date from check_result_jk where risk_market_item='是'
                union
                select 'jj1' company,item_count,problem_count,check_date from check_result_jj1 where risk_market_item='是'
                union
                select 'jj2' company,item_count,problem_count,check_date from check_result_jj2 where risk_market_item='是'
                union
                select 'jz' company,item_count,problem_count,check_date from check_result_jz where risk_market_item='是'
                ) a
                group by DATE_FORMAT(a.check_date,'%Y-%m-%d')
                having count(distinct company)=7
                order by 1 asc"""
        curs.execute(sql)
        result = curs.fetchall()
        return JsonResponse({'datatime': [r[0] for r in result], 'value': [r[1] for r in result]}, safe=False)
    except Exception as e:
        print(e)
        return JsonResponse({'msg': str(e)})
    finally:
        curs.close()
        conn.close()


@require_http_methods(['GET'])
def subcompany_problem_count(request):
    """
    子公司仪表盘-问题数据项统计
    :param request:
    :return:
    """
    year = request.GET.get('year')
    quarter = request.GET.get('quarter')
    month = request.GET.get('month')
    day = request.GET.get('day')
    company = request.GET.get('company')

    try:
        conn = db_config.mysql_connect()
        curs = conn.cursor()
        # 问题占比 | 问题数据总量 | 问题数据项
        sql = f"""select sum(a.item_count),sum(a.problem_count),a.check_item from (
                    select a.check_item,a.item_count,a.problem_count
                                        from check_result_{company} a  ,(select company,
                                         max(version) version from  check_result_version
                                        where company = '{company}'
                                        and year(check_date)={year}
                                        and quarter(check_date)={quarter}
                                        and month(check_date)={month}
                                        and day(check_date)={day}
                                        ) b                                  
                                        where a.check_version = b.version
                                        and year(check_date)={year}
										and quarter(check_date)={quarter}
										and month(check_date)={month}
                                        and day(check_date)={day}
                                        and a.problem_count is not null
                                        and a.problem_count !=0
                                        and a.risk_market_item='是'

                    ) a
                    group by a.check_item
                    order by 2 desc"""
        print(sql)
        curs.execute(sql)
        result = curs.fetchall()
        result_list = [['问题占比', '问题数据总量', '数据总量', '问题数据项'], ]
        for i in result:
            problem_per = (int(i[1]) / int(i[0]))
            problem_per = str(round(problem_per * 100, 2)) + "%"
            problem_total = int(i[1])
            item_total = int(i[0])
            item = i[2]
            result_list.append([problem_per, problem_total, item_total, item])
        return JsonResponse(result_list, safe=False)
    except Exception as e:
        print(e)
        return JsonResponse(http_response.repo().fail(str(e)))
    finally:
        curs.close()
        conn.close()


@require_http_methods(['POST'])
def home_page_info_label(request):
    """# 大屏首页展示数据
        数据标准数  质检模型数  校验项数1 规则数n 调度方案数 运行中"""
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    labels = []
    try:
        # 数据标准数
        sql_1 = """SELECT
                        'bzs' label_id,
                        '数据标准数' label_name,
                        count( 1 ) label_value 
                    FROM
                        data_standard_desc UNION ALL
                    SELECT
                        'zjmxs',
                        '质检模型数',
                        count( 1 ) cnt 
                    FROM
                        hbd_menu 
                    WHERE
                        menu_validity = 1 UNION ALL
                    SELECT
                        'ddfas',
                        '调度方案数',
                        count( 1 ) cnt 
                    FROM
                        django_apscheduler_djangojob UNION ALL
                    SELECT
                        'jyxs',
                        '校验项数',
                        count( 1 ) cnt 
                    FROM
                        check_result_template a,
                        check_result_detail_template b,
                        hbd_menu c 
                    WHERE
                        b.pid = a.id 
                        AND a.company = b.company
                        AND a.company = c.menu_name_en 
                        AND c.menu_validity = '1' 
                        AND b.effective=1 
                        AND a.status ='已启用' 
                        
                        
                        UNION ALL
                    SELECT
                        'gzs',
                        '规则数',
                        count( 1 ) cnt 
                    FROM
                        check_result_template a,
                        hbd_menu c 
                    WHERE
                        a.company = c.menu_name_en 
                        AND c.menu_validity = '1'
                        AND a.status ='已启用' UNION ALL
                    SELECT
                        'yxz',
                        '运行中',
                        count( 1 ) cnt
                    FROM
                        django_apscheduler_djangojob a,
                        hbd_menu b 
                    WHERE
                        a.id = b.menu_name_en
                        and b.menu_validity =1
        """
        curs.execute(sql_1)
        result_all = curs.fetchall()
        for result in result_all:
            labels.append(domains.label(result[1], result[0], result[2]).__dict__)
        result_json = http_response.repo().success(data=labels)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def home_page_info_model(request):
    """# 大屏首页展示数据 柱状图"""
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        # 数据标准数
        sql_1 = """select menu_name_en,menu_name cnt from hbd_menu where menu_validity = 1"""
        curs.execute(sql_1)
        result_base = curs.fetchall()
        all_result_arr = []
        for result in result_base:
            try:
                result_dic = {"model_id": result[0], "model_name": result[1]}
                menu_name_en = result[0]

                sql_2 = f"""SELECT a.id,a.result_set,b.id,b.type,b.result result_err,c.result
                        FROM
                            check_result_{menu_name_en} a
                            JOIN check_result_detail_{menu_name_en} b ON a.id = b.pid  AND a.check_version = b.check_version 
                            left join check_result_detail_{menu_name_en} c on c.check_version = b.check_version 
                            and b.denominator_order = c.category_order
                            and c.pid = b.pid
                        WHERE a.check_version = (select max(check_version) from check_result_{menu_name_en} where  company = '{menu_name_en}')
                        and a.result_set is not null
                        and b.type='numerator'
                        and c.type='denominator'"""
                curs.execute(sql_2)
                result_detail = curs.fetchall()
                error_count = 0
                all_count = 0
                for result1 in result_detail:
                    error_count = error_count+result1[4]
                    all_count = all_count+result1[5]

                result_dic["error_count"] = error_count
                result_dic["all_count"] = all_count

                all_result_arr.append(result_dic)
            except Exception as e:
                print(str(e))
                continue

        result_json = http_response.repo().success(data=all_result_arr)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def home_page_info_model(request):
    """绩效值"""
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        # 数据标准数
        sql_1 = """select menu_name_en,menu_name cnt from hbd_menu where menu_validity = 1"""
        curs.execute(sql_1)
        result_base = curs.fetchall()
        all_result_arr = []
        for result in result_base:
            try:
                result_dic = {"model_id": result[0], "model_name": result[1]}
                menu_name_en = result[0]
                sql_2 = f"""SELECT a.id,a.result_set,b.id,b.type,coalesce(b.result,0) result_err,coalesce(c.result,0)
                        FROM
                            check_result_{menu_name_en} a
                            JOIN check_result_detail_{menu_name_en} b 
                            ON a.id = b.pid  AND a.check_version = b.check_version 
                            left join check_result_detail_{menu_name_en} c on c.check_version = b.check_version 
                            and b.denominator_order = c.category_order
                            and c.pid = b.pid
                        WHERE a.check_version = (select max(check_version) from check_result_{menu_name_en} where  company = '{menu_name_en}')
                        and a.result_set is not null
                        and b.type='numerator'
                        and c.type='denominator'"""
                curs.execute(sql_2)
                result_detail = curs.fetchall()
                error_count = 0
                all_count = 0
                total_coefficient = 0
                count = 0
                for result1 in result_detail:
                    count = count + 1
                    error_count = error_count+int(result1[4])
                    all_count = all_count+int(result1[5])
                    if all_count != 0:
                        total_coefficient = total_coefficient + error_count/all_count

                result_dic["error_count"] = error_count
                result_dic["all_count"] = all_count
                # 系数计算
                result_dic["total_coefficient"] = (1 - round(total_coefficient/count, 4))*100 if count != 0 else 0

                all_result_arr.append(result_dic)
            except Exception as e:
                print(str(e))
                continue
        result_json = http_response.repo().success(data=all_result_arr)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def home_page_info_distribution(request):
    """
    :param request:
    :return:
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        # 数据标准数
        sql_1 = """SELECT   COALESCE( count( aa.pid ), 0 ) cnt,
                            d.dm,
                            d.cmc 
                        FROM
                            hbd_aa10 d
                            LEFT JOIN (
                            SELECT
                                a.id pid,
                                a.problem_type
                            FROM
                                check_result_template a
                                JOIN hbd_menu c ON a.company = c.menu_name_en 
                                AND c.menu_validity = '1' 
                            WHERE
                                a.STATUS = '已启用' 
                            ) aa ON d.dm = aa.problem_type 
                        WHERE
                            d.category = 'quality' 
                        GROUP BY
                            d.dm,
                            d.cmc"""
        curs.execute(sql_1)
        result_base = curs.fetchall()
        all_result_arr = []
        for result in result_base:
            result_dic = {"dm": result[1], "cmc": result[2], "value": result[0]}
            all_result_arr.append(result_dic)

        result_json = http_response.repo().success(data=all_result_arr)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_page_info_version(request):
    """ 单个model 下的数据  version 信息
    company  check_version"""
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = f"""SELECT
                    date_format( check_date, '%Y-%m-%d' ) date_str,
                    version check_version,
                    company,
                    b.menu_name 
                FROM
                    check_result_version a
                    LEFT JOIN hbd_menu b ON a.company = b.menu_name_en 
                    WHERE check_date >= '{param_dict.get("startTime")}'
                    AND check_date <=  '{param_dict.get("endTime")}'
                    AND check_date is not null
                order by version desc
                """
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_page_info_version_all(request):
    """ 单个model 下的数据  所有 version 信息
    company  check_version"""
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = f"""SELECT
                    concat(date_format( check_date, '%Y-%m-%d' ),'|',version) as id,
										date_format( check_date, '%Y年' ) pid,
                    date_format( check_date, '%Y-%m-%d' ) date_str,
                    version check_version,
                    company,
                    b.menu_name 
                FROM
                    check_result_version a
                    LEFT JOIN hbd_menu b ON a.company = b.menu_name_en 
                    WHERE  check_date is not null
                    and company = '{param_dict.get('company')}'
	union all 							
select year_str id,0 year_str1 ,year_str year_str2,year_str year_str3,year_str year_str4,year_str year_str5 from (								
select 		date_format( check_date, '%Y年' ) year_str
FROM
                    check_result_version a
                    LEFT JOIN hbd_menu b ON a.company = b.menu_name_en 
                    WHERE  check_date is not null		
                    AND company = '{param_dict.get('company')}'	
										group by 		date_format( check_date, '%Y年' )
										) aa
order by id	desc									
																				
                """
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_page_info_result_detail(request):
    """规则检查数据结果明细
        参数： company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = f"""SELECT  a.id pid,
                            a.note,
                            b.id sub_id,
                            b.type,
                            COALESCE ( b.result, 0 ) result_err,
                            COALESCE ( c.result, 0 ) result,
                            aa10.cmc,
                            b.t_name,
                            b.c_name 
                        FROM
                            check_result_{param_dict.get("company")} a
                            JOIN check_result_detail_{param_dict.get("company")} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{param_dict.get("company")} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid
                            LEFT JOIN hbd_aa10 aa10 ON aa10.category = 'quality' 
                            AND aa10.dm = a.problem_type 
                        WHERE a.check_version = {param_dict.get("check_version")}
                            AND a.result_set IS NOT NULL
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                            AND b.effective = '1' 
                            and a.status='已启用'
                        ORDER BY
                            pid,
                            note,
                            cmc
                """
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_json = http_response.repo().success(data=result_base)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


@require_http_methods(['POST'])
def model_page_info_result(request):
    """规则检查数据结果汇总
       参数： company  check_version
    """
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    try:
        all_params = request.body
        if all_params is None:
            return JsonResponse(http_response.repo().fail(msg='参数为空'))
        param_dict = json.loads(all_params)
        # 数据标准数
        sql_1 = f"""SELECT
                        id pid,
                        note
                    FROM
                        check_result_{param_dict.get("company")} a
                    WHERE  a.status='已启用'
                    and a.check_version = {param_dict.get("check_version")}
                        
                """
        curs.execute(sql_1)
        result_base = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
        result_arr = []
        for result in result_base:
            result_dic = {"rule_note": result.get("note"), "pid": result.get("pid")}
            sql_detail = f"""SELECT  a.id pid,
                            a.note,
                            b.id sub_id,
                            b.type,
                            COALESCE ( b.result, 0 ) result_err,
                            COALESCE ( c.result, 0 ) result,
                            aa10.cmc,
                            b.t_name,
                            b.c_name 
                        FROM
                            check_result_{param_dict.get("company")} a
                            JOIN check_result_detail_{param_dict.get("company")} b ON a.id = b.pid 
                            AND a.check_version = b.check_version
                            LEFT JOIN check_result_detail_{param_dict.get("company")} c ON c.check_version = b.check_version 
                            AND b.denominator_order = c.category_order 
                            AND c.pid = b.pid
                            LEFT JOIN hbd_aa10 aa10 ON aa10.category = 'quality' 
                            AND aa10.dm = a.problem_type 
                        WHERE a.check_version = {param_dict.get("check_version")}
                            AND a.result_set IS NOT NULL
                            AND b.type = 'numerator' 
                            AND c.type = 'denominator' 
                            AND b.effective = '1' 
                            and a.status='已启用'
                            and a.id = {result.get("pid")} """
            curs.execute(sql_detail)
            result_detail = custom_utils.arr_to_dictarr(curs.fetchall(), curs.description)
            error_count = 0
            all_count = 0
            total_coefficient = 0
            count = 0
            for result1 in result_detail:
                count = count + 1
                error_count = error_count+int(result1.get("result_err"))
                all_count = all_count+int(result1.get("result"))
                if all_count != 0:
                    total_coefficient = total_coefficient + error_count/all_count

            result_dic["error_count"] = error_count
            result_dic["all_count"] = all_count
            # 系数计算
            result_dic["total_coefficient"] = 1 - round(total_coefficient/count, 4)
            result_arr.append(result_dic)
        result_json = http_response.repo().success(data=result_arr)
    except Exception as e:
        result_json = http_response.repo().fail(msg=str(e))
    finally:
        curs.close()
        conn.close()
    return JsonResponse(result_json)


